﻿using Alos.Entity;
using Alos.IRepository;
using PetaPoco;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Alos.Repository
{
    public class SysUserInfoRepository : BaseRepository<SysUserInfo>, ISysUserInfoRepository
    {
        public SysUserInfo Login(string username, string password)
        {
            var sql = Sql.Builder.Append(" select A.ID,A.UserCode,A.UserName,A.SchoolID,A.GradeCode,A.GradeName,A.ClassID,A.ClassName,A.SubjectCode,A.SubjectName,A.RoleId,A.Card,A.Sex,A.Birthday,A.Nation,A.Origin,A.Political,A.Head,A.Motto,B.RoleName from SysUserInfo A ");
            sql.LeftJoin(" SysRoleInfo B ").On(" A.RoleId=B.ID ");
            sql.Where(" A.UserCode=@0 and A.PassWord=@1 ", username, password);

            return DBContext.SingleOrDefault<SysUserInfo>(sql);
        }
        /// <summary>
        /// 根据班级编号获取用户列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IEnumerable<SysUserInfo> GetUserByClassId(int id, int roleid = 3)
        {
            return DBContext.Query<SysUserInfo>(" select A.ID,A.UserCode,A.UserName,A.SchoolID,A.GradeCode,A.GradeName,A.ClassID,A.ClassName,A.SubjectCode,A.SubjectName,A.RoleId,A.Card,A.Sex,A.Birthday,A.Nation,A.Origin,A.Political,A.Head,A.Motto,B.RoleName from SysUserInfo A left join SysRoleInfo B on A.RoleId=B.ID where ClassID=@0 and RoleId=@1 ", id, roleid);
        }
        public SysUserInfo GetCurUser(int id)
        {
            return DBContext.SingleOrDefault<SysUserInfo>(" select A.ID,A.UserCode,A.UserName,A.SchoolID,A.GradeCode,A.GradeName,A.ClassID,A.ClassName,A.SubjectCode,A.SubjectName,A.RoleId,A.Card,A.Sex,A.Birthday,A.Nation,A.Origin,A.Political,A.Head,A.Motto,B.RoleName from SysUserInfo A left join SysRoleInfo B on A.RoleId=B.ID where A.ID=@0 ", id);
        }

        /// <summary>
        /// 根据教室编号获取学生信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public IEnumerable<SysUserInfo> GetStudentByClassId(int cid)
        {
            var sql = Sql.Builder.Append("  select A.ID,A.UserCode,A.UserName,A.SchoolID,A.GradeCode,A.GradeName,A.ClassID,A.ClassName,A.SubjectCode,A.SubjectName,A.RoleId,A.Card,A.Sex,A.Birthday,A.Nation,A.Origin,A.Political,A.Head,A.Motto,B.RoleName from SysUserInfo A left join SysRoleInfo B on A.RoleId=B.ID where ClassID=@0 and A.RoleId=(SELECT b.Id FROM SysRoleInfo B WHERE B.RoleName='学生') ", cid);

            return DBContext.Query<SysUserInfo>(sql);
        }
    }
}
